package zorm

import (
	"database/sql"
	"fmt"
	"github.com/gohouse/converter"
	"time"
	"gitee.com/zouyang8210/zjson"
	"gitee.com/zouyang8210/zlogs"
)

// New 初始化数据库操作对像
func New(userName, password, serverAddr, databaseName, port string) (db Db, err error) {
	db.dns = userName + ":" + password + "@tcp(" + serverAddr + ":" + port + ")/" + databaseName + "?charset=utf8"
	if err = db.open(); err != nil {
		return
	}
	db.db.SetMaxOpenConns(100)
	db.db.SetMaxIdleConns(20)
	db.recycle = false
	return
}

// SetMaxConn 设备最大连接数和闲置连接数,0为不限置
func (c *Db) SetMaxConn(openConn, idleConn int) {
	c.db.SetMaxIdleConns(idleConn)
	c.db.SetMaxOpenConns(openConn)
}

// SetDebug 开户/关闭Debug模式
func (c *Db) SetDebug(v bool) {
	c.Debug = v
}

func (c *Db) SetRecycle(v bool) {
	c.recycle = v
}

// Query 执行查询sql语句
func (c *Db) Query(strSql string, tx *Tx) (record RecordSet, err error) {
	if c.db.Ping() != nil {
		err = c.open()
	}
	if c.recycle {
		defer func() { _ = c.Close() }()
	}
	var stmt *sql.Stmt
	var rows *sql.Rows

	c.logs(strSql)
	now := time.Now()
	if tx == nil {
		defer c.printDuration(now)
		stmt, err = c.db.Prepare(strSql)
	} else {
		defer c.printDuration(now)
		stmt, err = tx.tx.Prepare(strSql)
	}
	if err != nil {
		c.errLogs(strSql, err.Error())
		return
	}
	defer func() {
		err = stmt.Close()
	}()
	if rows, err = stmt.Query(); err == nil {
		defer func() {
			err = rows.Close()
		}()
		record, err = c.getRecordSet(rows)
	} else {
		c.errLogs(strSql, err.Error())
	}

	return
}

// Execute 执行增,删,改 sql语句
func (c *Db) Execute(strSql string, tx *Tx) (result int64, err error) {
	if c.db.Ping() != nil {
		err = c.open()
	}
	if c.recycle {
		defer func() {
			err = c.Close()
		}()
	}

	var r sql.Result
	now := time.Now()
	defer func() {
		c.printDuration(now)
	}()

	c.logs(strSql)
	if tx == nil {
		r, err = c.db.Exec(strSql)
	} else {
		r, err = tx.tx.Exec(strSql)
	}
	if err != nil {
		c.errLogs(strSql, err.Error())
		return
	}
	result, err = r.RowsAffected()
	return
}

// NewCondition 创建条件对像
func (c *Db) NewCondition() (cond Conditions) {
	cond = Conditions{}
	return
}

// Insert 新增(插入)记录
func (c *Db) Insert(tbName string, obj interface{}, tx *Tx, flag bool, fields ...string) (result int64, err error) {
	var data map[string]interface{}
	if data, err = c.createMapData(obj, flag, fields...); err != nil {
		return
	}
	var strSql string
	if strSql, err = c.createInsertSQL(tbName, data); err == nil {
		result, err = c.Execute(strSql, tx)
	}
	return
}

// Update 更新记录
func (c *Db) Update(tbName string, obj interface{}, cond Conditions, tx *Tx, flag bool, fields ...string) (result int64, err error) {
	var data map[string]interface{}
	if data, err = c.createMapData(obj, flag, fields...); err != nil {
		return
	}
	if strSql := c.createUpdateSql(tbName, data, cond); strSql != EMPTY {
		result, err = c.Execute(strSql, tx)
	}
	return
}

// UpdateSingle 更新单个字段
func (c *Db) UpdateSingle(tbName string, field string, value interface{}, cond Conditions, tx *Tx) (result int64, err error) {
	var obj = map[string]interface{}{
		field: value,
	}
	//var data map[string]interface{}
	//if data, err = c.createMapData(obj, true); err != nil {
	//	return
	//}
	if strSql := c.createUpdateSql(tbName, obj, cond); strSql != EMPTY {
		result, err = c.Execute(strSql, tx)
	}
	return
}

// Delete 删除记录
func (c *Db) Delete(tbName string, cond Conditions, tx *Tx) (result int64, err error) {
	strCond, _ := c.createCondition(cond)
	strSql := fmt.Sprintf("DELETE FROM %s  %s", tbName, strCond)
	result, err = c.Execute(strSql, tx)
	return
}

// QueryRecordSet 查询返回记录集
func (c *Db) QueryRecordSet(tbName string, cond Conditions, tx *Tx, fields ...string) (record RecordSet, err error) {
	strCond, _ := c.createCondition(cond)
	strFields := ""
	if len(fields) == 0 {
		strFields = " * "
	} else {
		for _, v := range fields {
			strFields += v + ","
		}
		strFields = strFields[0 : len(strFields)-1]
	}
	strSql := fmt.Sprintf("SELECT %s FROM %s  %s", strFields, tbName, strCond)
	record, err = c.Query(strSql, tx)
	return
}

// QueryObject 查询输出对像
func (c *Db) QueryObject(tbName string, cond Conditions, obj interface{}, tx *Tx, fields ...string) (err error) {
	strCond, _ := c.createCondition(cond)
	strFields := ""
	if len(fields) == 0 {
		strFields = " * "
	} else {
		for _, v := range fields {
			strFields += v + ","
		}
		strFields = strFields[0 : len(strFields)-1]
	}
	record := RecordSet{}
	strSql := fmt.Sprintf("SELECT %s FROM %s  %s", strFields, tbName, strCond)
	record, err = c.Query(strSql, tx)
	if record.Data != nil && record.Count > 0 {
		err = zjson.ObjToObj(&obj, record.Data)
	}
	return
}

// QuerySingle 查询单条记录
func (c *Db) QuerySingle(tbName string, cond Conditions, obj interface{}, tx *Tx, fields ...string) (err error) {
	cond.Limit(1)
	strCond, _ := c.createCondition(cond)

	strFields := ""
	if len(fields) == 0 {
		strFields = " * "
	} else {
		for _, v := range fields {
			strFields += v + ","
		}
		strFields = strFields[0 : len(strFields)-1]
	}
	record := RecordSet{}
	strSql := fmt.Sprintf("SELECT %s FROM %s  %s", strFields, tbName, strCond)
	record, err = c.Query(strSql, tx)
	if record.Count > 0 {
		err = zjson.ObjToObj(&obj, record.Data[0])
	}
	return
}

// 获取数据行数
func (c *Db) GetRowsCount(tbName string, cond Conditions) (count int64, err error) {
	cond.ClearOther()
	stat := struct {
		Count int64 `json:"count"`
	}{}
	if err = c.QuerySingle(tbName, cond, &stat, nil, "COUNT(*) as count"); err != nil {
		return
	}
	count = stat.Count
	return
}

// QueryForProcedure 存储过程执行查询
func (c *Db) QueryForProcedure(proName string, parameterValue []interface{}, outObject interface{}) (err error) {
	strValue := c.createProcedureValue(parameterValue)
	strSql := ""
	if strValue != EMPTY {
		strSql = fmt.Sprintf("CALL %s(%s)", proName, strValue)
	} else {
		strSql = fmt.Sprintf("CALL %s", proName)
	}
	record := RecordSet{}
	if record, err = c.Query(strSql, nil); err != nil {
		return
	}
	if record.Count > 0 {
		err = zjson.ObjToObj(&outObject, record.Data)
	}
	return
}

// ExecForProcedure 存储过程执行修改
func (c *Db) ExecForProcedure(proName string, parameterValue []interface{}, tx *Tx) (result int64, err error) {
	strValue := c.createProcedureValue(parameterValue)
	strSql := ""
	if strValue != EMPTY {
		strSql = fmt.Sprintf("CALL %s(%s)", proName, strValue)
	} else {
		strSql = fmt.Sprintf("CALL %s", proName)
	}
	result, err = c.Execute(strSql, tx)
	return
}

func (c *Db) Close() error {
	return c.db.Close()
}

//=================================================事务处理=============================================================

// Begin 开始(创建)事务
func (c *Db) Begin() (transaction Tx, err error) {
	if c.db.Ping() != nil {
		err = c.open()
	}
	transaction.tx, err = c.db.Begin()
	return
}

// Commit 事务提交
func (c *Tx) Commit() (err error) {
	err = c.tx.Commit()
	return
}

// Rollback 事务回滚
func (c *Tx) Rollback() (err error) {
	err = c.tx.Rollback()
	return
}

//=====================================================================================================================

func (c *Db) TableIsExist(tableName string) bool {
	strSql := fmt.Sprintf("select * from information_schema.`TABLES` where table_name='%s' limit 1", tableName)
	record, e := c.Query(strSql, nil)
	if e == nil && record.Count > 0 {
		return true
	}
	return false
}

// 把数据操作错误信息，记录到表
func (c *Db) errLogs(sql, errMsg string) {
	msg := fmt.Sprintf("sql 执行出错：%s:%s", errMsg, sql)
	zlogs.Error(msg)
	zlogs.WriteLogs("data_err", msg)
	return
}

func (c *Db) CreateTableStruct(tableName string, filePath string) (err error) {
	if filePath == "" {
		filePath = "./table_structure.json"
	}
	// 初始化
	t2t := converter.NewTable2Struct()
	// 个性化配置
	t2t.Config(&converter.T2tConfig{
		//RmTagIfUcFirsted: false,// 如果字段首字母本来就是大写, 就不添加tag, 默认false添加, true不添加
		//TagToLower: false, // tag的字段名字是否转换为小写, 如果本身有大写字母的话, 默认false不转
		//UcFirstOnly: false,// 字段首字母大写的同时, 是否要把其他字母转换为小写,默认false不转换
		//SeperatFile: false,// 每个struct放入单独的文件,默认false,放入同一个文件(暂未提供)
	})
	// 开始迁移转换
	if tableName != "" {
		t2t = t2t.Table(tableName) // 指定某个表,如果不指定,则默认全部表都迁移
	}
	//t2t = t2t.Prefix("prefix_")// 表前缀
	//t2t = t2t.EnableJsonTag(true)    // 是否添加json tag
	t2t = t2t.PackageName(tableName) // 生成struct的包名(默认为空的话, 则取名为: package model)
	t2t = t2t.TagKey("json")         // tag字段的key值,默认是orm
	//t2t = t2t.RealNameMethod("TableName")// 是否添加结构体方法获取表名
	t2t = t2t.SavePath(filePath) // 生成的结构体保存路径
	dns := c.dns
	//fmt.Println(dns)
	t2t = t2t.Dsn(dns) // 数据库dsn,这里可以使用 t2t.DB() 代替,参数为 *sql.DB 对象
	err = t2t.Run()    // 执行
	return
}
